﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Merchants
{
    internal static class AdminDAL
    {
        internal static AdminEntity GetAdminByReader(IDataReader dr)
        {
            AdminEntity admin = new AdminEntity();

            admin.AdminID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            admin.ShopID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            admin.Account = dr.IsDBNull(2) ? null : dr.GetString(2);
            admin.Password = dr.IsDBNull(3) ? null : dr.GetString(3);
            admin.Logins = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            admin.LastDateTime = Util.ConverDateTime(dr.GetValue(5));
            admin.Created = Util.ConverDateTime(dr.GetValue(6));

            return admin;
        }

        internal static int GetAdminCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectAdmin = @"SELECT COUNT([AdminID]) FROM [Shop_Admin]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAdmin, ref parameterlst);
            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectAdmin);
            DbHelper.PackDbCommandByDbParameters(SelectAdminCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectAdminCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<AdminEntity> GetAdminList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AdminEntity> adminlst = new List<AdminEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAdmin = @"
                    SELECT [AdminID],[ShopID],[Account],[Password],[Logins],[LastDateTime],[Created]
                    FROM (
                        SELECT [AdminID],[ShopID],[Account],[Password],[Logins],[LastDateTime],[Created], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAdmin += " " + sortField + " " + sortMode + ",";
            SelectAdmin += @" Shop_Admin.[AdminID] Desc
                    ) AS rowNum 
                        FROM Shop_Admin ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAdmin, ref parameterlst);
            SelectAdmin += @"
                    ) AS [admin] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectAdmin);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectAdminCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAdminCommand))
            {
                while (dr.Read())
                {
                    AdminEntity admin = GetAdminByReader(dr);

                    adminlst.Add(admin);
                }
            }

            return adminlst;
        }

        internal static AdminEntity GetAdmin(int adminID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            AdminEntity admin = null;

            string SelectAdmin = @"
                    SELECT [AdminID],[ShopID],[Account],[Password],[Logins],[LastDateTime],[Created]
                    FROM Shop_Admin 
                    WHERE Shop_Admin.AdminID=@AdminID
                ";

            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectAdmin);
            db.AddInParameter(SelectAdminCommand, "@AdminID", DbType.Int32, adminID);

            using (IDataReader dr = db.ExecuteReader(SelectAdminCommand))
            {
                if (dr.Read())
                {
                    admin = GetAdminByReader(dr);
                }
            }

            return admin;
        }

        internal static AdminEntity GetAdminByAccount(string account)
        {
            Database db = DatabaseFactory.CreateDatabase();
            AdminEntity admin = null;

            string SelectAdmin = @"
                    SELECT [AdminID],[ShopID],[Account],[Password],[Logins],[LastDateTime],[Created]
                    FROM Shop_Admin 
                    WHERE Account=@Account ";

            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectAdmin);
            db.AddInParameter(SelectAdminCommand, "@Account", DbType.String, account);

            using (IDataReader dr = db.ExecuteReader(SelectAdminCommand))
            {
                if (dr.Read())
                {
                    admin = GetAdminByReader(dr);
                }
            }

            return admin;
        }

        internal static int UpdateAdmin(AdminEntity admin)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateAdmin = @"UPDATE [Shop_Admin] 
				 SET [AdminID]=@AdminID,[ShopID]=@ShopID,[Account]=@Account,[Password]=@Password,[Logins]=@Logins,[LastDateTime]=@LastDateTime,[Created]=@Created 
				 WHERE [AdminID] = @AdminID";

            DbCommand UpdateAdminCommand = db.GetSqlStringCommand(UpdateAdmin);
            db.AddInParameter(UpdateAdminCommand, "@AdminID", DbType.Int32, admin.AdminID);
            db.AddInParameter(UpdateAdminCommand, "@ShopID", DbType.Int32, admin.ShopID);
            db.AddInParameter(UpdateAdminCommand, "@Account", DbType.String, admin.Account);
            db.AddInParameter(UpdateAdminCommand, "@Password", DbType.String, admin.Password);
            db.AddInParameter(UpdateAdminCommand, "@Logins", DbType.Int32, admin.Logins);
            db.AddInParameter(UpdateAdminCommand, "@LastDateTime", DbType.DateTime, admin.LastDateTime);
            db.AddInParameter(UpdateAdminCommand, "@Created", DbType.DateTime, admin.Created);

            result = db.ExecuteNonQuery(UpdateAdminCommand);

            return result;
        }

        internal static int HitAdmin(int adminID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAdmin = @"UPDATE [Shop_Admin]
                    SET [Logins] = [Logins]+1
                    WHERE [AdminID] = @AdminID";

            DbCommand SetAdminCommand = db.GetSqlStringCommand(SetAdmin);
            db.AddInParameter(SetAdminCommand, "@AdminID", DbType.Int32, adminID);
            result = db.ExecuteNonQuery(SetAdminCommand);

            return result;
        }

        internal static int Enter(string email, string password)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SelectEnter = @"
                /*   1：成功  2：会员不存在  3：密码错误  4：帐号还未启用   */
	            DECLARE @result AS INT
	            SET @result=1
	
	            IF EXISTS(SELECT [Account] FROM Shop_Admin WHERE [Account]=@Account)
	            BEGIN
			        IF EXISTS(SELECT [Account] FROM Shop_Admin WHERE [Account]=@Account AND [Password]=@Password)
			        BEGIN
                        IF EXISTS(SELECT Shop_Admin.[Account] 
                            FROM Shop_Admin INNER JOIN Shop ON Shop_Admin.ShopID = Shop.ShopID 
                            WHERE Shop.[State]=1 AND Shop_Admin.[Account]=@Account)
			            BEGIN
				            SET @result=1
			            END
			            ELSE
			            BEGIN
				            SET @result=4
			            END
			        END
			        ELSE
			        BEGIN
				        SET @result=3
			        END
	            END
	            ELSE
	            BEGIN
		            SET @result=2
	            END
	            SELECT @result";

            DbCommand SetMemberPasswordCommand = db.GetSqlStringCommand(SelectEnter);
            db.AddInParameter(SetMemberPasswordCommand, "@Password", DbType.String, password);
            db.AddInParameter(SetMemberPasswordCommand, "@Account", DbType.String, email);
            object obj = db.ExecuteScalar(SetMemberPasswordCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());
            return result;
        }

        internal static int SetAdminPassword(int adminID, string oldPassword, string newPassword)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetMemberPassword = @"
            /*   1：成功  2：会员不存在  3：原密码错误   */
	        DECLARE @result AS INT
	        SET @result=0
        	
	        IF EXISTS(SELECT AdminID FROM Shop_Admin WHERE AdminID=@AdminID)
	        BEGIN
		        IF EXISTS(SELECT AdminID FROM Shop_Admin WHERE AdminID=@AdminID AND [Password]=@OldPassword)
		        BEGIN
			        UPDATE [Shop_Admin] SET [Password] = @NewPassword WHERE [AdminID] = @AdminID
			        SET @result=1
		        END
		        ELSE
		        BEGIN
			        SET @result=3
		        END
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
	        SELECT @result";

            DbCommand SetMemberPasswordCommand = db.GetSqlStringCommand(SetMemberPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@OldPassword", DbType.String, oldPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@NewPassword", DbType.String, newPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@AdminID", DbType.Int32, adminID);
            object obj = db.ExecuteScalar(SetMemberPasswordCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());
            return result;
        }

        internal static int ResetPass(int adminID, string newpass)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string ResetPass = @"
            /* 1：成功 2：没有此会员 */
	        DECLARE @result AS INT
	        SET @result=1
        	
	        IF EXISTS(SELECT [AdminID] FROM [Shop_Admin] WHERE [AdminID]=@AdminID)
	        BEGIN
		        UPDATE [Shop_Admin] SET [Password]=@NewPass WHERE [AdminID]=@AdminID
		        SET @result=1
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
        	
	        SELECT @result";

            DbCommand ResetPassCommand = db.GetSqlStringCommand(ResetPass);
            db.AddInParameter(ResetPassCommand, "@AdminID", DbType.Int32, adminID);
            db.AddInParameter(ResetPassCommand, "@NewPass", DbType.String, newpass);
            object obj = db.ExecuteScalar(ResetPassCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static int FindPass(string email, string newpass)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string FindPass = @"
            /* 1：成功 2：没有此会员 */
	        DECLARE @result AS INT
	        SET @result=1
        	
	        IF EXISTS(SELECT [MemberID] FROM [Shop_Admin] WHERE [Account]=@Account)
	        BEGIN
		        UPDATE [Shop_Admin] SET [Password]=@NewPass WHERE [Account]=@Account
		        SET @result=1
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
        	
	        SELECT @result";


            DbCommand ResetPassCommand = db.GetSqlStringCommand(FindPass);
            db.AddInParameter(ResetPassCommand, "@Account", DbType.String, email);
            db.AddInParameter(ResetPassCommand, "@NewPass", DbType.String, newpass);
            object obj = db.ExecuteScalar(ResetPassCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }
    }
}
